SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_GetPortalRoles]
	@PortalId     int

AS
	SELECT R.RoleId,
		   R.PortalId,
		   R.RoleGroupId,
		   R.RoleName,
		   R.Description,
		   R.ServiceFee,
		   case when convert(int,R.ServiceFee) <> 0 then R.BillingPeriod else null end AS BillingPeriod,
		   case when convert(int,R.ServiceFee) <> 0 then L1.Text else '' end AS BillingFrequency,
		   R.TrialFee,
		   case when R.TrialFrequency <> 'N' then R.TrialPeriod else null end AS TrialPeriod,
		   case when R.TrialFrequency <> 'N' then L2.Text else '' end AS TrialFrequency,
		   R.IsPublic,
		   R.AutoAssignment,
		   R.RSVPCode,
		   R.IconFile,
		   R.Status,
		   R.SecurityMode,
		   (SELECT COUNT(*) FROM dbo.DnnProduct_UserRoles U WHERE U.RoleID = R.RoleID) AS UserCount,
		   R.CreatedByUserID,
		   R.CreatedOnDate,
		   R.LastModifiedByUserID,
		   R.LastModifiedOnDate
	FROM dbo.DnnProduct_Roles R
	LEFT OUTER JOIN dbo.DnnProduct_Lists L1 ON R.BillingFrequency = L1.Value AND L1.ListName='Frequency'
	LEFT OUTER JOIN dbo.DnnProduct_Lists L2 ON R.TrialFrequency = L2.Value AND L2.ListName='Frequency'
	WHERE  ( R.PortalId = @PortalId OR R.PortalId is null )
	ORDER BY R.RoleName
GO
